﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;
using DateBaseConnectionPool;

namespace TestMySqlConnetPoll
{
    public struct objUser
    {
        public int UserId;
        public string Name;
        public string Password;
        public byte UserType;

        public override string ToString()
        {
            return Name;
        }
        public objUser(int id, string s, string p, int t)
        {
            UserId = id;
            Name = s;
            Password = p;
            UserType = (byte)t;
        }
    }
    public class UserTable
    {
        #region 变量定义 用户列表
        
        public List<objUser> UserList = new List<objUser>();
        public int ErrorFlag = 0;
        public bool ReadyUserList = false;
        #endregion
        #region 用户列表
        private Mutex keyMutex = new Mutex();
        static private int keykey = 0;
        public void ReadUserList()
        {
            UserList.Clear();
            ErrorFlag = 0;
            ReadyUserList = false;
            objUser obj= new objUser();
            ThreadPool.QueueUserWorkItem(new WaitCallback(ThreadReadUserList), obj);
        }

        public  bool InsertTable(objUser obj)
        {
            bool tmpFlag = false;
            string TableName = "UserTable";
            
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                    tmpFlag = false;
                }
                if (con != null)
                {
                    StringBuilder ds = new StringBuilder();
                    ds.Append(string.Format("delete from {0} where userId = {1}", TableName, obj.UserId));
                    MySqlCommand dc = new MySqlCommand(ds.ToString(), con);
                    try
                    {
                        dc.ExecuteNonQuery();       //如果是更新，需要先删除记录
                    }
                    catch (MySqlException ex)
                    {
                        ErrorFlag = 2;
                        //写异常日志
                    }

                    StringBuilder InsertStatement = new StringBuilder();
                    InsertStatement.Append(string.Format("insert into {0}(userId,Name,password,type) ", TableName));
                    InsertStatement.Append(" values(?userId,?Name,?password,?type)");
                    MySqlCommand InsertCommand = new MySqlCommand(InsertStatement.ToString(), con);
                    InsertCommand.Parameters.Add(new MySqlParameter("?userId", MySqlDbType.Byte)).Value = obj.UserId;
                    InsertCommand.Parameters.Add(new MySqlParameter("?Name", MySqlDbType.VarChar, 20)).Value = obj.Name;
                    InsertCommand.Parameters.Add(new MySqlParameter("?password", MySqlDbType.VarChar, 20)).Value = obj.Password;
                    InsertCommand.Parameters.Add(new MySqlParameter("?type", MySqlDbType.Byte)).Value = obj.UserType;

                    try
                    {
                        int k = InsertCommand.ExecuteNonQuery();
                        if (k == 1) tmpFlag = true;        //写入成功
                        else tmpFlag = false;              //写入失败
                    }
                    catch (MySqlException e)
                    {
                        ErrorFlag = 3;
                        //写异常日记
                        tmpFlag = false;       //写入失败
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        ErrorFlag = 4;
                        //写异常日记
                    }
                }
            }
            return tmpFlag;
        }

        public bool DelUser(objUser obj)
        {
            bool tmpFlag = false;
            string TableName = "UserTable";
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                }

                if (con != null)
                {
                    StringBuilder ds = new StringBuilder();
                    ds.Append(string.Format("delete from {0} where userId = {1}", TableName, obj.UserId));
                    MySqlCommand dc = new MySqlCommand(ds.ToString(), con);

                    try
                    {
                        int k = dc.ExecuteNonQuery();       //如果是更新，需要先删除记录
                        if (k == 1) tmpFlag = true;        //删除成功
                        else tmpFlag = false;              //删除失败
                    }
                    catch (MySqlException e)
                    {
                        ErrorFlag = 2;
                        //写异常日记
                        tmpFlag = false;       //写入失败
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        ErrorFlag = 3;
                        //写异常日记
                    }
                }
            }
            return tmpFlag;
        }

        private void ThreadReadUserList(object o)
        {
            UserList.Clear();
            string TableName = "UserTable";
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                }
                if (con != null)
                {
                    StringBuilder SelectStatement = new StringBuilder();

                    SelectStatement.Append(string.Format("select userId,Name,password,type from {0}", TableName));

                    try
                    {
                        MySqlCommand selectCommand = new MySqlCommand(SelectStatement.ToString(), con);
                        selectCommand.CommandTimeout = 120;   //120s操作超时

                        MySqlDataReader read = selectCommand.ExecuteReader();
                        while (read.Read())
                        {
                            objUser t = new objUser();
                            t.UserId = read.GetInt32(read.GetOrdinal("userId"));
                            if (!read.IsDBNull(read.GetOrdinal("Name")))
                            {
                               t.Name =  read.GetString(read.GetOrdinal("Name"));
                            }
                            if (!read.IsDBNull(read.GetOrdinal("password")))
                            {
                               t.Password =  read.GetString(read.GetOrdinal("password"));
                            }
                            
                            t.UserType = read.GetByte(read.GetOrdinal("type"));
                            UserList.Add(t);
                        }
                        read.Close();
                        ReadyUserList = true;
                    }
                    catch (MySqlException e)
                    {
                        //写错误日志
                        ErrorFlag = 2;
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        //写错误日志
                        ErrorFlag = 3;
                    }
                }
                else ErrorFlag = 6;
            }
            else ErrorFlag = 7;
        }
        #endregion
    }
}
